import datetime

from sqlalchemy.orm import Session
from sqlalchemy import text

import models
import schemas


def get_record_chart(db: Session, scope_ids: [int]):
    res = db.query(models.Record.user_id, text('sum(fee)'), text('extract(month from created_at) as month')) \
        .filter(models.Record.state == True, models.Record.user_id.in_(scope_ids),
                text('extract(year from created_at) = extract(year from now())')) \
        .group_by(models.Record.user_id) \
        .group_by(text('month')) \
        .all()
    data = {}
    for r in res:
        index = r[2] - 1
        if index not in data:
            data[index] = {}
        data[index][r[0]] = r[1]
    data2 = []
    for i in range(12):
        item = {}
        for uid in scope_ids:
            item[uid] = data[i][uid] if i in data and uid in data[i] else 0
        data2.append(item)
    return data2


def get_project_chart(db: Session, scope_ids: [int]):
    res = db.query(models.Project.user_id, text('sum(fee)'), text('sum(income)')) \
        .filter(models.Project.state == True, models.Project.user_id.in_(scope_ids)) \
        .group_by(models.Project.user_id) \
        .all()
    data = {}
    for r in res:
        data[r[0]] = {'fee': r[1], 'income': r[2]}
    for uid in scope_ids:
        if uid not in data:
            data[uid] = {'fee': 0, 'income': 0}
    return data


def get_card_chart(db: Session, scope_ids: [int]):
    res = db.query(models.Card.user_id, text('sum(fee)')) \
        .filter(models.Card.state == True, models.Card.user_id.in_(scope_ids)) \
        .group_by(models.Card.user_id) \
        .all()
    data = {}
    for r in res:
        data[r[0]] = r[1]
    for uid in scope_ids:
        if uid not in data:
            data[uid] = 0
    return data


def get_stats_chart(db: Session, scope_ids: [int]):
    res = db.query(models.Statistics.user_id, text('deposit + finance'),
                   text('extract(month from created_at) as month')) \
        .filter(models.Statistics.state == True, models.Statistics.user_id.in_(scope_ids),
                text('extract(year from created_at) = extract(year from now())')) \
        .all()
    data = {}
    for r in res:
        index = r[2] - 1
        if index not in data:
            data[index] = {}
        data[index][r[0]] = r[1]
    data2 = []
    for i in range(12):
        item = {}
        for uid in scope_ids:
            if i == datetime.datetime.now().month - 1:
                res2 = db.execute(text(
                    'select c.deposit + p.finance from (select sum(fee) as deposit from _card where state=true and user_id = :uid) c, (select sum(fee) as finance from _project where state=true and user_id = :uid) p'), {'uid': uid}).first()
                item[uid] = 0 if res2[0] is None else res2[0]
            else:
                item[uid] = data[i][uid] if i in data else 0
        data2.append(item)
    return data2
